package com.dudu.log.ticketLog.dao;

import java.sql.Connection;
import java.util.List;

import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;

import core.CoreDao;
import core.db.DBConnectionUtil;

/**
 * 话单推送信息dao
 * 
 * @author jack
 * 
 */
public class TicketPushDao {

	/**
	 * 查询条数（分页用）
	 * 
	 * @param paramSql
	 * @return
	 */
	public int getTotalAcount(CoreDao dao, String paramSql) {
		if (StringUtils.isNotBlank(paramSql)) {
			paramSql = " and " + paramSql;
		}
		Connection con = null;
		int result = -1;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer();
			sql.append("select count(session_id) as totalAcount from ( ");
			sql.append("select t1.session_id,t1.begin_time,t1.end_time,t1.caller,t1.callee,t1.status,t1.note,t1.ability_type,t1.send_time time from t_push_ticket t1 ");
			sql.append("union all ");
			sql.append("select t2.session_id,t2.begin_time,t2.end_time,t2.caller,t2.callee,'99','',t2.ability_type,t2.push_time time from t_push_ticket_log t2 ) t ");
			if (StringUtils.isNotBlank(paramSql)) {
				sql.append("where 1=1 ").append(paramSql);
			}
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] {});

			JSONObject obj = list.get(0);
			result = (Integer) obj.get("totalAcount");

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}

	/**
	 * 查询数据（分页）
	 * 
	 * @param paramSql
	 * @param pageSql
	 * @return
	 */
	public List<JSONObject> queryAll(CoreDao dao, String paramSql, String pageSql) {
		Connection con = null;
		List<JSONObject> result = null;
		if (StringUtils.isNotBlank(paramSql)) {
			paramSql = " and " + paramSql;
		}
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer();
			sql.append("select session_id,begin_time,end_time,caller,callee,status,note,ability_type,push_time,time,record_url from ( ");
			sql.append("select t1.session_id,t1.begin_time,t1.end_time,t1.caller,t1.callee,t1.status,t1.note,t1.ability_type,t1.send_time time0,t1.time,'' push_time,record_url  from t_push_ticket t1 ");
			sql.append("union all ");
			sql.append("select t2.session_id,t2.begin_time,t2.end_time,t2.caller,t2.callee,'99' status,'' note,t2.ability_type,t2.push_time time0,t2.time,t2.push_time,record_url from t_push_ticket_log t2 ) t ");
			if (StringUtils.isNotBlank(paramSql)) {
				sql.append("where 1=1 ").append(paramSql);
			}
			sql.append("order by time0 desc");
			sql.append(pageSql);
			result = dao.serviceJsonSql(con, sql.toString(), new Object[] { null });
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}

	/**
	 * 查询条数-推送失败（分页用）
	 * 
	 * @param paramSql
	 * @return
	 */
	public int getTotalAcountFailPush(CoreDao dao, String paramSql) {
		if (StringUtils.isNotBlank(paramSql)) {
			paramSql = " and " + paramSql;
		}
		Connection con = null;
		int result = -1;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer();
			sql.append(" SELECT count(id) as totalAcount FROM t_push_ticket ");
			if (StringUtils.isNotBlank(paramSql)) {
				sql.append("where 1=1 ").append(paramSql);
			}
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] {});

			JSONObject obj = list.get(0);
			result = (Integer) obj.get("totalAcount");

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}

	/**
	 * 查询数据-推送失败（分页）
	 * 
	 * @param paramSql
	 * @param pageSql
	 * @return
	 */
	public List<JSONObject> queryAllFailPush(CoreDao dao, String paramSql, String pageSql) {
		Connection con = null;
		List<JSONObject> result = null;
		if (StringUtils.isNotBlank(paramSql)) {
			paramSql = " and " + paramSql;
		}
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer();
			sql.append(" SELECT * FROM t_push_ticket ");
			if (StringUtils.isNotBlank(paramSql)) {
				sql.append("where 1=1 ").append(paramSql);
			}
			sql.append("order by id desc");
			sql.append(pageSql);
			result = dao.serviceJsonSql(con, sql.toString(), new Object[] { null });
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	
	/**
	 * 推送失败的话单重推
	 * @param dao
	 * @param sqlpara
	 * @return
	 */
	public int repushTicket(CoreDao dao, String sqlparam) {

		Connection con = null;
		int result = -1;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			if (StringUtils.isNotBlank(sqlparam)) {
				sqlparam = " and " + sqlparam;
			}
			StringBuffer sql = new StringBuffer(" update t_push_ticket set status=0,pid='',note='',code='' where 1=1  ");
			sql.append(sqlparam);
			
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] {});

			JSONObject obj = list.get(0);
			result = obj.optInt("result");

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}

}
